#!/bin/bash

# 定义变量方便修改
table_db=cy_database 
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else 
	do_date=`date -d "-1 day" +%F`  
fi 

sql="
set hive.exec.dynamic.partition.mode=nonstrict;
with
tmp_probation_people_info as (
	select
		id,
		name,
		null sex,
		birthday,
		cast(round(months_between('2020-06-17', birthday)/12) as string) as age,
		nation,
		idcard,
		null census_register_area_code,
		null census_type,
		null area_code,
		phone,
		null contact_qq,
		null contact_wei,
		education,
		school,
		edu_time,
		graduation,
		null census_register,
		home_address,
		null startup_code,
		case when ent_name is not null then '1' else null end as personal_state_code,
		null ent_id ,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		null position_code,
		null position,
		probation_start_time entry_work,
		null out_work,
		cast(round(months_between(current_date(),edu_time)/12) as string) work_year
	from cy_database.dwd_probation_people_info ppi
	where ppi.dt='2020-06-17' and ppi.id is not null

),


tmp_enterprise_person_info as(
	select 
		id,
		name,
		sex,
		birthday,
		cast(round(months_between(current_date() , birthday)/12 ) as string) age,
		null nation,
		null idcard,
		native_code census_register_area_code, 
		null census_type,
		area_code,
		phone,
		null contact_qq ,
		null contact_wei ,
		education,
		school,
		null edu_time,
		null graduation,
		native_place census_register,
		home_address,
		null startup_code,
		cast('1' as string ) personal_state_code,
		ent_id,
		ent_name,
		industry_code ,
		industry_type,
		business_code ,
		business_type ,
		position_code,
		position,
		entry_work ,
		out_work,
		work_year
	from cy_database.dwd_enterprise_person_info epi
	where epi.dt='2020-06-17' and epi.id is not null


)

insert overwrite table cy_database.dws_person_work_detail partition(dt='2020-06-17')
	select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		startup_code,
		personal_state_code,
		ent_id,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		position_code,
		position,
		entry_work,
		out_work,
		work_year
	from tmp_probation_people_info

    union
	select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		startup_code,
		personal_state_code,
		ent_id,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		position_code,
		position,
		entry_work,
		out_work,
		work_year
	from tmp_enterprise_person_info
;



"
$hive -e "$sql"